<?php
$this -> pageTitle = Yii::app() -> name . ' - Statistiche';
$this -> breadcrumbs = array('Statistiche', );

$connection = Yii::app() -> db;
?>
<div id="header">
	<div class="headerImage"><img src="images/chart2.png" />
	</div>
	<div class="titolo">
		Statistiche
	</div>
	<div class="sottotitolo">
		Ricavi e statistiche varie
	</div>
	<div class="clear"></div>
	<div id="breadcrumbs-one">
		<?php $this -> widget('zii.widgets.CBreadcrumbs', array('links' => $this -> breadcrumbs, )); ?>
	</div>
</div>
<div id="statisticheLayout">
	<h2>Ricavi giornata</h2>
	<table class="statistiche">
		<?php
		$command = $connection -> createCommand("select Trattamenti.nome, sum(prezzo) as 'Totale'
	from Appuntamenti
	join Trattamenti on Appuntamenti.trattamento = Trattamenti.id
	where data >= date(now()) and data < date(now()) + interval 1 day
	group by Trattamenti.nome");

		$dataReader = $command -> query();
		$totaleGiornata = 0;
		foreach ($dataReader as $row) {
			echo '<tr><td>' . $row['nome'] . '</td><td>' . number_format($row['Totale'], 2, ',', '.') . ' €</td></tr>';
			$totaleGiornata += $row['Totale'];
		}
		echo '<tr><th>Totale</th><th>' . number_format($totaleGiornata, 2, ',', '.') . ' €</th></tr>';
		?>
	</table>

	<h2>Trattamenti per operatore</h2>
	<table class="statistiche">
		<?php
		echo '<tr><th>Nome</th><th>Trattamenti</th><th>Minuti (ore)</th></tr>';
		
		$command = $connection -> createCommand("SELECT id as oper, nomeOperatore, count.cc, ore.summ
FROM Operatori 
LEFT JOIN (
	SELECT count(*) as cc,operatore
	FROM Appuntamenti as a
	WHERE DATE(data)=DATE(now())
	GROUP BY operatore
) as count ON count.operatore = id
LEFT JOIN (
	SELECT sum(Trattamenti.durata) as summ,operatore FROM Appuntamenti 
	JOIN Trattamenti ON Appuntamenti.trattamento = Trattamenti.id
	WHERE DATE(Appuntamenti.data)=DATE(now())
	GROUP BY operatore
) as ore ON ore.operatore = id");

		$dataReader = $command -> query();
		$totaleMinuti = 0;
		$totaleTratt = 0;
		foreach ($dataReader as $row) {
			echo '<tr><td>' . $row['nomeOperatore'] . '</td><td>';
			if ($row['cc']=='') echo '-';
			else echo $row['cc'];
			echo '</td><td>';
			if ($row['summ']=='') echo '0';
			else echo $row['summ']. " (".($row['summ']/60).")";
			echo '</td></tr>';
			
			$totaleMinuti += $row['summ'];
			$totaleTratt += $row['cc'];
		}
		echo '<tr><th></th><th>'.$totaleTratt.'</th><th>'.$totaleMinuti.' ('.($totaleMinuti/60).')</th></tr>';
		?>
	</table>
	
		<h2>Trattamenti per operatore negli ultimi 30 giorni</h2>
	<table class="statistiche">
		<?php
		echo '<tr><th>Nome</th><th>Trattamenti</th><th>Minuti (ore)</th></tr>';
		
		$command = $connection -> createCommand("SELECT id as oper, nomeOperatore, count.cc, ore.summ
FROM Operatori 
LEFT JOIN (
	SELECT count(*) as cc,operatore
	FROM Appuntamenti as a
	WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= DATE(data)
	GROUP BY operatore
) as count ON count.operatore = id
LEFT JOIN (
	SELECT sum(Trattamenti.durata) as summ,operatore FROM Appuntamenti 
	JOIN Trattamenti ON Appuntamenti.trattamento = Trattamenti.id
	WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= DATE(data)
	GROUP BY operatore
) as ore ON ore.operatore = id");

		$dataReader = $command -> query();
		$totaleMinuti = 0;
		$totaleTratt = 0;
		foreach ($dataReader as $row) {
			echo '<tr><td>' . $row['nomeOperatore'] . '</td><td>';
			if ($row['cc']=='') echo '-';
			else echo $row['cc'];
			echo '</td><td>';
			if ($row['summ']=='') echo '0';
			else echo $row['summ']. " (".($row['summ']/60).")";
			echo '</td></tr>';
			
			$totaleMinuti += $row['summ'];
			$totaleTratt += $row['cc'];
		}
		echo '<tr><th></th><th>'.$totaleTratt.'</th><th>'.$totaleMinuti.' ('.($totaleMinuti/60).')</th></tr>';
		?>
	</table>
	
</div>